from DataAccess.DBConnFactory import DBConnFactory
from Misc.Utils import *

from string import Template


def query_exposure(ref_date, tick_list=None, portf_list=None):
	
	sql_tpl = Template('''	
	select dp.lticker sec_ticker,
		s.name sec_name,
		decode(sign(amount),1,'LONG','SHORT') sec_sign,
		dp.security_type sec_type,
		mexp.exposure/fx.price/pmv.mkt_val mkt_exposure,
		p.name portf_name
	from daily_position_tick_ex dp
	left join comm_security_static_info s 
		on s.ticker=dp.lticker 
	join portfolio_info p 
		on dp.portfolio_id=p.portfolio_id
	join security_type_rank r 
		on r.sec_type=dp.security_type 
	join mkt_expo mexp 
		on mexp.ref_date=TO_DATE('${DATE}','yyyy-mm-dd')
		and mexp.portfolio_id=dp.portfolio_id 
		and mexp.security_type=dp.security_type
		and mexp.ticker=dp.ticker 
	join portfolio_mkt_val pmv 
		on pmv.ref_date=TO_DATE('${DATE}','yyyy-mm-dd')
		and pmv.portfolio_id=dp.portfolio_id
	join comm_fx_latest fx 
		on fx.ref_date=TO_DATE('${DATE}','yyyy-mm-dd')
		and fx.dom_curncy=dp.price_currency 
		and fx.for_curncy=p.currency	
	where dp.ref_date=TO_DATE('${DATE}','yyyy-mm-dd')
		and ${PORTF_CRITERIA} 
		and ${TICK_CRITERIA}
	order by dp.portfolio_id,r.rank,dp.lticker
	''')	
	
	sql_text = sql_tpl.substitute(DATE=ref_date.isoformat(), 
								TICK_CRITERIA=format_sql_criteria('dp.lticker', tick_list), 
								PORTF_CRITERIA=format_sql_criteria('dp.portfolio_id', portf_list))	
								
	#print sql_text
	#with open('D:/work/pke/testcases/dump.log', 'w') as log_file:
	#	log_file.write(sql_text)
	
	cursor = DBConnFactory().get_db_connection('PKEDB').cursor()							
	cursor.execute(sql_text)
	return cursor.fetchall()
	